package com.pai4j.zwiki.service.diagram;

import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import net.sourceforge.plantuml.FileFormat;
import net.sourceforge.plantuml.FileFormatOption;
import net.sourceforge.plantuml.SourceStringReader;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.io.FileOutputStream;
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.util.*;

/**
 * 数据库图表生成服务
 * 生成ER图和数据库表结构图
 *
 * @author pai4j
 * @since 2025-10-12
 */
@Slf4j
@Service
@RequiredArgsConstructor
public class DatabaseDiagramService {

    private final JdbcTemplate jdbcTemplate;

    @Value("${zwiki.workspace.base-path:/tmp/zwiki-workspace}")
    private String workspaceBasePath;

    /**
     * 生成数据库ER图
     *
     * @param taskId 任务ID
     * @param databaseName 数据库名称
     * @return ER图文件路径
     */
    public String generateERDiagram(String taskId, String databaseName) {
        log.info("生成ER图: taskId={}, database={}", taskId, databaseName);

        try {
            // 获取数据库表信息
            List<TableInfo> tables = getDatabaseTables(databaseName);
            
            if (tables.isEmpty()) {
                log.warn("数据库中没有表，生成示例ER图");
                return generateSampleERDiagram(taskId);
            }

            // 生成PlantUML代码
            StringBuilder uml = new StringBuilder();
            uml.append("@startuml\n");
            uml.append("' ER图\n");
            uml.append("!define primary_key(x) <u><b>x</b></u>\n");
            uml.append("!define foreign_key(x) <i>x</i>\n");
            uml.append("!define unique(x) <color:green>x</color>\n");
            uml.append("skinparam linetype ortho\n\n");

            // 为每个表生成实体
            for (TableInfo table : tables) {
                generateEntityDefinition(uml, table);
            }

            // 添加表之间的关系
            addTableRelationships(uml, tables);

            uml.append("\n@enduml");

            return generateDiagram(taskId, "er-diagram", uml.toString());

        } catch (Exception e) {
            log.error("生成ER图失败", e);
            return generateSampleERDiagram(taskId);
        }
    }

    /**
     * 生成数据库表结构图
     *
     * @param taskId 任务ID
     * @param databaseName 数据库名称
     * @return 表结构图文件路径集合
     */
    public Map<String, String> generateTableStructureDiagrams(String taskId, String databaseName) {
        log.info("生成表结构图: taskId={}, database={}", taskId, databaseName);

        Map<String, String> diagrams = new HashMap<>();

        try {
            List<TableInfo> tables = getDatabaseTables(databaseName);
            
            if (tables.isEmpty()) {
                log.warn("数据库中没有表，生成示例表结构图");
                String sampleDiagram = generateSampleTableStructure(taskId);
                diagrams.put("sample_table", sampleDiagram);
                return diagrams;
            }

            // 为每个表生成详细结构图
            for (TableInfo table : tables) {
                String diagramPath = generateSingleTableStructure(taskId, table);
                diagrams.put(table.getTableName(), diagramPath);
            }

        } catch (Exception e) {
            log.error("生成表结构图失败", e);
        }

        return diagrams;
    }

    /**
     * 生成综合数据库结构图
     * 包含所有表和字段信息
     */
    public String generateComprehensiveDatabaseDiagram(String taskId, String databaseName) {
        log.info("生成综合数据库结构图: taskId={}, database={}", taskId, databaseName);

        try {
            List<TableInfo> tables = getDatabaseTables(databaseName);
            
            if (tables.isEmpty()) {
                return generateSampleERDiagram(taskId);
            }

            StringBuilder uml = new StringBuilder();
            uml.append("@startuml\n");
            uml.append("' 综合数据库结构图\n");
            uml.append("skinparam classAttributeIconSize 0\n");
            uml.append("skinparam linetype ortho\n\n");

            // 按模块分组（通过表名前缀）
            Map<String, List<TableInfo>> moduleGroups = groupTablesByModule(tables);

            for (Map.Entry<String, List<TableInfo>> entry : moduleGroups.entrySet()) {
                String moduleName = entry.getKey();
                List<TableInfo> moduleTables = entry.getValue();

                uml.append("package \"").append(moduleName).append(" 模块\" {\n");

                for (TableInfo table : moduleTables) {
                    uml.append("  class ").append(table.getTableName()).append(" {\n");
                    
                    for (ColumnInfo column : table.getColumns()) {
                        String symbol = getFieldSymbol(column);
                        uml.append("    ").append(symbol).append(" ")
                           .append(column.getColumnName()).append(": ")
                           .append(column.getDataType());
                        
                        if ("PRI".equals(column.getColumnKey())) {
                            uml.append(" <<PK>>");
                        }
                        
                        uml.append("\n");
                    }
                    
                    uml.append("  }\n");
                }

                uml.append("}\n\n");
            }

            // 添加关系
            addTableRelationships(uml, tables);

            uml.append("\n@enduml");

            return generateDiagram(taskId, "comprehensive-database", uml.toString());

        } catch (Exception e) {
            log.error("生成综合数据库结构图失败", e);
            return null;
        }
    }

    // ========== 私有辅助方法 ==========

    /**
     * 获取数据库表信息
     */
    private List<TableInfo> getDatabaseTables(String databaseName) {
        List<TableInfo> tables = new ArrayList<>();

        try {
            String sql = "SELECT TABLE_NAME, TABLE_COMMENT FROM information_schema.TABLES " +
                        "WHERE TABLE_SCHEMA = ? AND TABLE_TYPE = 'BASE TABLE'";
            
            List<Map<String, Object>> tableList = jdbcTemplate.queryForList(sql, databaseName);

            for (Map<String, Object> tableRow : tableList) {
                String tableName = (String) tableRow.get("TABLE_NAME");
                String tableComment = (String) tableRow.get("TABLE_COMMENT");

                TableInfo table = new TableInfo();
                table.setTableName(tableName);
                table.setTableComment(tableComment);
                table.setColumns(getTableColumns(databaseName, tableName));

                tables.add(table);
            }

        } catch (Exception e) {
            log.error("获取数据库表信息失败: {}", databaseName, e);
        }

        return tables;
    }

    /**
     * 获取表的列信息
     */
    private List<ColumnInfo> getTableColumns(String databaseName, String tableName) {
        List<ColumnInfo> columns = new ArrayList<>();

        try {
            String sql = "SELECT COLUMN_NAME, DATA_TYPE, COLUMN_KEY, IS_NULLABLE, " +
                        "COLUMN_DEFAULT, COLUMN_COMMENT " +
                        "FROM information_schema.COLUMNS " +
                        "WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? " +
                        "ORDER BY ORDINAL_POSITION";

            List<Map<String, Object>> columnList = jdbcTemplate.queryForList(sql, databaseName, tableName);

            for (Map<String, Object> columnRow : columnList) {
                ColumnInfo column = new ColumnInfo();
                column.setColumnName((String) columnRow.get("COLUMN_NAME"));
                column.setDataType((String) columnRow.get("DATA_TYPE"));
                column.setColumnKey((String) columnRow.get("COLUMN_KEY"));
                column.setIsNullable((String) columnRow.get("IS_NULLABLE"));
                column.setColumnDefault((String) columnRow.get("COLUMN_DEFAULT"));
                column.setColumnComment((String) columnRow.get("COLUMN_COMMENT"));

                columns.add(column);
            }

        } catch (Exception e) {
            log.error("获取表列信息失败: {}.{}", databaseName, tableName, e);
        }

        return columns;
    }

    /**
     * 生成实体定义
     */
    private void generateEntityDefinition(StringBuilder uml, TableInfo table) {
        uml.append("entity \"").append(table.getTableName()).append("\" {\n");
        
        if (table.getTableComment() != null && !table.getTableComment().isEmpty()) {
            uml.append("  --").append(table.getTableComment()).append("--\n");
        }

        // 主键
        for (ColumnInfo column : table.getColumns()) {
            if ("PRI".equals(column.getColumnKey())) {
                uml.append("  * ").append(column.getColumnName())
                   .append(" : ").append(column.getDataType())
                   .append(" <<PK>>\n");
            }
        }

        uml.append("  --\n");

        // 普通字段
        for (ColumnInfo column : table.getColumns()) {
            if (!"PRI".equals(column.getColumnKey())) {
                String nullable = "YES".equals(column.getIsNullable()) ? "" : "*";
                uml.append("  ").append(nullable).append(" ")
                   .append(column.getColumnName())
                   .append(" : ").append(column.getDataType());
                
                if ("MUL".equals(column.getColumnKey())) {
                    uml.append(" <<FK>>");
                }
                
                if (column.getColumnComment() != null && !column.getColumnComment().isEmpty()) {
                    uml.append(" // ").append(column.getColumnComment());
                }
                
                uml.append("\n");
            }
        }

        uml.append("}\n\n");
    }

    /**
     * 添加表之间的关系
     */
    private void addTableRelationships(StringBuilder uml, List<TableInfo> tables) {
        uml.append("' 表关系\n");

        for (TableInfo table : tables) {
            for (ColumnInfo column : table.getColumns()) {
                if ("MUL".equals(column.getColumnKey())) {
                    // 外键关系
                    String referencedTable = guessReferencedTable(column.getColumnName(), tables);
                    if (referencedTable != null) {
                        uml.append(table.getTableName())
                           .append(" }o--|| ")
                           .append(referencedTable)
                           .append(" : ")
                           .append(column.getColumnName())
                           .append("\n");
                    }
                }
            }
        }
    }

    /**
     * 推测被引用的表
     */
    private String guessReferencedTable(String columnName, List<TableInfo> tables) {
        // 常见命名模式: user_id -> user, order_id -> order
        String guessedTableName = columnName.replace("_id", "");
        
        for (TableInfo table : tables) {
            if (table.getTableName().equalsIgnoreCase(guessedTableName) ||
                table.getTableName().equalsIgnoreCase(guessedTableName + "s")) {
                return table.getTableName();
            }
        }
        
        return null;
    }

    /**
     * 按模块分组表
     */
    private Map<String, List<TableInfo>> groupTablesByModule(List<TableInfo> tables) {
        Map<String, List<TableInfo>> groups = new LinkedHashMap<>();

        for (TableInfo table : tables) {
            String moduleName = extractModuleName(table.getTableName());
            groups.computeIfAbsent(moduleName, k -> new ArrayList<>()).add(table);
        }

        return groups;
    }

    /**
     * 提取模块名称
     */
    private String extractModuleName(String tableName) {
        // 常见模式: sys_user -> sys, order_info -> order
        if (tableName.contains("_")) {
            String[] parts = tableName.split("_");
            return parts[0];
        }
        return "core";
    }

    /**
     * 获取字段符号
     */
    private String getFieldSymbol(ColumnInfo column) {
        if ("PRI".equals(column.getColumnKey())) {
            return "+";
        } else if ("MUL".equals(column.getColumnKey())) {
            return "#";
        } else {
            return "-";
        }
    }

    /**
     * 生成单个表的结构图
     */
    private String generateSingleTableStructure(String taskId, TableInfo table) {
        StringBuilder uml = new StringBuilder();
        uml.append("@startuml\n");
        uml.append("' ").append(table.getTableName()).append(" 表结构\n");
        uml.append("skinparam classAttributeIconSize 0\n\n");

        uml.append("class ").append(table.getTableName()).append(" {\n");
        
        if (table.getTableComment() != null && !table.getTableComment().isEmpty()) {
            uml.append("  ' ").append(table.getTableComment()).append("\n");
        }

        for (ColumnInfo column : table.getColumns()) {
            String symbol = getFieldSymbol(column);
            uml.append("  ").append(symbol).append(" ")
               .append(column.getColumnName()).append(": ")
               .append(column.getDataType());
            
            List<String> attributes = new ArrayList<>();
            if ("PRI".equals(column.getColumnKey())) {
                attributes.add("PK");
            }
            if ("NO".equals(column.getIsNullable())) {
                attributes.add("NOT NULL");
            }
            if ("MUL".equals(column.getColumnKey())) {
                attributes.add("FK");
            }
            
            if (!attributes.isEmpty()) {
                uml.append(" <<").append(String.join(",", attributes)).append(">>");
            }
            
            if (column.getColumnComment() != null && !column.getColumnComment().isEmpty()) {
                uml.append(" ' ").append(column.getColumnComment());
            }
            
            uml.append("\n");
        }

        uml.append("}\n");
        uml.append("\n@enduml");

        return generateDiagram(taskId, "table-" + table.getTableName(), uml.toString());
    }

    /**
     * 生成示例ER图（当无法连接数据库时）
     */
    private String generateSampleERDiagram(String taskId) {
        StringBuilder uml = new StringBuilder();
        uml.append("@startuml\n");
        uml.append("' 示例ER图\n");
        uml.append("!define primary_key(x) <u><b>x</b></u>\n");
        uml.append("!define foreign_key(x) <i>x</i>\n");
        uml.append("skinparam linetype ortho\n\n");

        // 用户表
        uml.append("entity \"user\" {\n");
        uml.append("  * id : BIGINT <<PK>>\n");
        uml.append("  --\n");
        uml.append("  * username : VARCHAR(50)\n");
        uml.append("  * password : VARCHAR(100)\n");
        uml.append("  * email : VARCHAR(100)\n");
        uml.append("  * status : TINYINT\n");
        uml.append("  * create_time : DATETIME\n");
        uml.append("}\n\n");

        // 角色表
        uml.append("entity \"role\" {\n");
        uml.append("  * id : BIGINT <<PK>>\n");
        uml.append("  --\n");
        uml.append("  * role_name : VARCHAR(50)\n");
        uml.append("  * description : VARCHAR(200)\n");
        uml.append("  * create_time : DATETIME\n");
        uml.append("}\n\n");

        // 用户角色关联表
        uml.append("entity \"user_role\" {\n");
        uml.append("  * id : BIGINT <<PK>>\n");
        uml.append("  --\n");
        uml.append("  * user_id : BIGINT <<FK>>\n");
        uml.append("  * role_id : BIGINT <<FK>>\n");
        uml.append("  * create_time : DATETIME\n");
        uml.append("}\n\n");

        // 权限表
        uml.append("entity \"permission\" {\n");
        uml.append("  * id : BIGINT <<PK>>\n");
        uml.append("  --\n");
        uml.append("  * permission_name : VARCHAR(50)\n");
        uml.append("  * resource_url : VARCHAR(200)\n");
        uml.append("  * create_time : DATETIME\n");
        uml.append("}\n\n");

        // 角色权限关联表
        uml.append("entity \"role_permission\" {\n");
        uml.append("  * id : BIGINT <<PK>>\n");
        uml.append("  --\n");
        uml.append("  * role_id : BIGINT <<FK>>\n");
        uml.append("  * permission_id : BIGINT <<FK>>\n");
        uml.append("  * create_time : DATETIME\n");
        uml.append("}\n\n");

        // 关系
        uml.append("user ||--o{ user_role\n");
        uml.append("role ||--o{ user_role\n");
        uml.append("role ||--o{ role_permission\n");
        uml.append("permission ||--o{ role_permission\n");

        uml.append("\n@enduml");

        return generateDiagram(taskId, "er-diagram-sample", uml.toString());
    }

    /**
     * 生成示例表结构图
     */
    private String generateSampleTableStructure(String taskId) {
        StringBuilder uml = new StringBuilder();
        uml.append("@startuml\n");
        uml.append("' 示例表结构\n");
        uml.append("skinparam classAttributeIconSize 0\n\n");

        uml.append("class user {\n");
        uml.append("  + id: BIGINT <<PK,NOT NULL>>\n");
        uml.append("  - username: VARCHAR(50) <<NOT NULL>>\n");
        uml.append("  - password: VARCHAR(100) <<NOT NULL>>\n");
        uml.append("  - email: VARCHAR(100)\n");
        uml.append("  - phone: VARCHAR(20)\n");
        uml.append("  - status: TINYINT <<NOT NULL>>\n");
        uml.append("  - create_time: DATETIME <<NOT NULL>>\n");
        uml.append("  - update_time: DATETIME\n");
        uml.append("}\n");

        uml.append("\n@enduml");

        return generateDiagram(taskId, "table-structure-sample", uml.toString());
    }

    /**
     * 生成PlantUML图表
     */
    private String generateDiagram(String taskId, String diagramName, String plantUmlCode) {
        try {
            Path diagramDir = Paths.get(workspaceBasePath, taskId, "diagrams");
            Files.createDirectories(diagramDir);

            String pngFilename = diagramName + ".png";
            Path pngFilePath = diagramDir.resolve(pngFilename);

            SourceStringReader reader = new SourceStringReader(plantUmlCode);
            try (FileOutputStream fos = new FileOutputStream(pngFilePath.toFile())) {
                reader.outputImage(fos, new FileFormatOption(FileFormat.PNG));
            }

            log.info("数据库图表生成成功: {}", pngFilePath);
            return pngFilePath.toString();

        } catch (IOException e) {
            log.error("生成数据库图表失败", e);
            return null;
        }
    }

    // ========== 内部类 ==========

    /**
     * 表信息
     */
    @lombok.Data
    private static class TableInfo {
        private String tableName;
        private String tableComment;
        private List<ColumnInfo> columns = new ArrayList<>();
    }

    /**
     * 列信息
     */
    @lombok.Data
    private static class ColumnInfo {
        private String columnName;
        private String dataType;
        private String columnKey;
        private String isNullable;
        private String columnDefault;
        private String columnComment;
    }
}

